import datetime
import json

import cx_Oracle

"""递归查询oracle存储关联的存储和表"""

user = "pigpos"
passwd = "pigpos"
listener = 'NMDCFARM.cpchina.cn/NMDCFARM'
conn = cx_Oracle.connect(user, passwd, listener)
cursor = conn.cursor()


def select(sql) -> json:
    rows = []
    cursor.execute(sql)
    result = cursor.fetchall()
    col_name = cursor.description
    for row in result:
        d = {}
        for col in range(len(col_name)):
            key = col_name[col][0]
            value = row[col]
            if isinstance(value, datetime.datetime):
                value = value.strftime('%Y-%m-%d %H:%M:%S')
            d[key] = value
        rows.append(d)
    js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
    return js


# 关闭连接，释放资源
def disconnect():
    cursor.close()
    conn.close()
    print("关闭数据库连接")


def ge_sql(org_code):
    # 检查数据
    sql = "select * from FR_DOCUMENT_RUNNING t where t.user_create like '%win%'  and t.org_code = '" + org_code + "' and t.document_type ='64'";
    d = select(sql)
    d = json.loads(d)
    # print(d)
    # 6431221000000
    for r in d:
        doc_no = str(r['RUNNING_NO'])
        # print(doc_no)
        sql = "select t.condition_01 as val from GD2_FR_MAS_TYPE t where  gd_type = 'SDC' and t.gd_code = '" + r[
            'SUB_DOCUMENT_TYPE'] + "'"
        val = json.loads(select(sql))
        type = '00'
        if len(val):
            if type is not None:
                type = val[0]['VAL']
        doc_no_new = doc_no.replace('2200', '22' + type)
        # doc_no_new = doc_no.replace('2200', '22' + type)
        # print(r['SUB_DOCUMENT_TYPE'] + '\t' + doc_no_new)
        sub_type = r['SUB_DOCUMENT_TYPE']
        update_sql = "update FR_DOCUMENT_RUNNING t set t.running_no = '" + doc_no_new + "' where t.user_create like '%win%'  and t.org_code = '" + org_code \
                     + "' and t.document_type ='64' and t.sub_document_type = '" + sub_type + "';"
        print(update_sql)


org_codes = ['250222040', '151022054', '142422086', '142422085', '142422088', '142422083', '110722235', '151122039',
             '161622015', '120322029', '1518220131', '156022128', '250222039', '170222044', '175522038', '142422084',
             '150622069', '175522040', '250222041', '111022317', '1518220132', '110122329', '110722236', '110122330',
             '111422119', '170222046', '170222045', '111422120', '170222048', '142422087', '160122034', '130322059',
             '170222047', '130122045', '175522037', '156022127', '150622068', '160122035']
if __name__ == '__main__':
    for org_code in org_codes:
        print('--分公司代码:' + org_code)
        ge_sql(org_code)
        print('\n\n\n')
    # a = '123456'
    # print(a[1:2])

disconnect()
